﻿// --------------------------------------------------------------------------------------------------------------------
// <copyright file="Access2003ConversionHelper.cs" company="CodePlex">
//   Copyright © CodePlex 2012
// </copyright>
// <summary>
//   Defines the Access2003ConversionHelper type.
// </summary>
// --------------------------------------------------------------------------------------------------------------------

namespace DataFrameworkGenerator.Helpers
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.OleDb;
    using System.Diagnostics;
    using System.Linq;

    using DataFrameworkGenerator.Factories;
    using DataFrameworkGenerator.Interfaces;

    /// <summary>
    /// This helpler assists with converting a Access2003 schema into objects that can be built by the generator.
    /// </summary>
    public class Access2003ConversionHelper : IConversionHelper
    {
        /// <summary>
        /// The type we use for all parameters that need the database type specified.
        /// </summary>
        private const DatabaseType DatabaseTypeForHelper = DatabaseType.Access2003;

        /// <summary>
        /// Gets or sets the target schema.
        /// </summary>
        /// <value>The target schema.</value>
        public string TargetSchema { get; set; }

        /// <summary>
        /// Converts the type of the data type int to db data.
        /// </summary>
        /// <param name="metadata">The metadata.</param>
        /// <returns>a propertly formated DbType name.</returns>
        public string ConvertDataTypeToDbDataType(ColumnMetadata metadata)
        {
            string convertedType;
            switch (metadata.ProviderDataType)
            {
                case 3: // Confirmed.
                    convertedType = "DbType.Int32";
                    break;
                case 4: // Confirmed.
                    convertedType = "DbType.Single";
                    break;
                case 6: // Confirmed.
                    convertedType = "DbType.Currency";
                    break;
                case 7: // Confirmed.
                    convertedType = "DbType.Date";
                    break;
                case 11: // Confirmed.
                    convertedType = "DbType.Boolean";
                    break;
                case 202: // Confirmed.
                    convertedType = "DbType.String";
                    break;
                default:
                    throw new InvalidOperationException(string.Format("Can not convert provided DB type \"{0}\" to a DbType.", metadata.DataType));
            }

            return convertedType;
        }

        /// <summary>
        /// Converts the type of the data type int to code.
        /// </summary>
        /// <param name="metadata">The metadata.</param>
        /// <returns>a properly formatted c# variable type.</returns>
        public string ConvertDataTypeToCodeType(ColumnMetadata metadata)
        {
            return metadata.DataType.Name;
        }

        /// <summary>
        /// Fetches the db tables.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <returns>
        /// A list containing all the tables in the provided schema.
        /// </returns>
        public IList<string> FetchDbTables(string connectionString)
        {
            List<string> tables = null;
            var connection = ConnectionFactory.GenerateConnection(DatabaseTypeForHelper, connectionString);

            var conn = connection as OleDbConnection;
            if (conn != null)
            {
                using (conn)
                {
                    conn.Open();

                    // Restrictions =  {TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE}
                    // TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_GUID, DESCRIPTION, TABLE_PROPID, DATE_CREATED, DATE_MODIFIED
                    var tblRestrictions = new[] { null, null, null, "TABLE" };
                    var schemaTables = conn.GetSchema("tables", tblRestrictions);
                    tables = (from DataRow t in schemaTables.Rows select t["TABLE_NAME"].ToString()).ToList();

                    conn.Close();
                }
            }

            return tables ?? new List<string>();
        }

        /// <summary>
        /// Fetches the table column metadata.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="tableName">Name of the table.</param>
        /// <returns>A list containing each column and it's metadata.</returns>
        public IList<ColumnMetadata> FetchTableColumnMetadata(string connectionString, string tableName)
        {
            var columnMetadatas = new List<ColumnMetadata>();
            var connection = ConnectionFactory.GenerateConnection(DatabaseTypeForHelper, connectionString);

            var conn = connection as OleDbConnection;
            if (conn != null)
            {
                using (conn)
                {
                    conn.Open();

                    var tempCommand = new OleDbCommand(string.Format("SELECT * FROM {0}", tableName), conn);
                    var tempReader = tempCommand.ExecuteReader(CommandBehavior.KeyInfo);
                    if (tempReader != null)
                    {
                        var schemaInfo = tempReader.GetSchemaTable();

                        if (schemaInfo != null)
                        {
                            foreach (DataRow rowColumn in schemaInfo.Rows)
                            {
                                var newMetadata = new ColumnMetadata
                                    {
                                        ColumnName = (string)rowColumn["ColumnName"],
                                        DataType = (Type)rowColumn["DataType"],
                                        IsAutoIncrementField = (bool)rowColumn["IsAutoIncrement"],
                                        IsKeyField = (bool)rowColumn["IsKey"],
                                        NullableField = (bool)rowColumn["AllowDBNull"],
                                        ProviderDataType = (int)rowColumn["ProviderType"],
                                        ColumnOrdinal = (int)rowColumn["ColumnOrdinal"],
                                        ColumnSize = (int)rowColumn["ColumnSize"],
                                        NumericPrecision = (short)rowColumn["NumericPrecision"],
                                        NumericScale = (short)rowColumn["NumericScale"],
                                        IsLong = (bool)rowColumn["IsLong"],
                                        IsReadOnly = (bool)rowColumn["IsReadOnly"],
                                        IsRowVersion = (bool)rowColumn["IsRowVersion"],
                                        IsUnique = (bool)rowColumn["IsUnique"],
                                        BaseSchemaName = rowColumn["BaseSchemaName"],  //////////////////
                                        BaseCatalogName = rowColumn["BaseCatalogName"] == DBNull.Value ? null : (string)rowColumn["BaseCatalogName"],
                                        BaseTableName = rowColumn["BaseTableName"] == DBNull.Value ? null : (string)rowColumn["BaseTableName"],
                                        BaseColumnName = rowColumn["BaseColumnName"] == DBNull.Value ? null : (string)rowColumn["BaseColumnName"]
                                    };

                                columnMetadatas.Add(newMetadata);
                            }
                        }
                        else
                        {
                            Debug.WriteLine("Could not find schema information table.");
                        }
                    }
                    else
                    {
                        Debug.WriteLine("Could not create Data Reader to fetch access schema.");
                    }

                    conn.Close();
                }
            }

            return columnMetadatas;
        }
    }
}
